Aggregate Functions in SQL
In this lesson, we will learn about the different aggregate functions available in SQL.
Aggregate functions in SQL#
In database management, an aggregate function is a function where the values of multiple rows are grouped together to form a single value of more significant meaning.
We will discuss the following in this lesson:
COUNT()SUM()AVG()MIN()MAX()
Again we will be using the CUSTOMERS table.
The COUNT function#
The COUNT() function returns the number of rows that match a specified criterion.
Syntax#
The syntax for the COUNT() function is as follows:
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
This query will return the number of Non-Null values in the specified column.
Example#
Let’s say we apply the COUNT function to the salary column:
The following code shows the SQL query:
As we can see it returned the number of Non-Null values over the column salary i.e, 6.
The SUM function#
The SUM() function returns the total sum of a numeric column.
Syntax#
The syntax for the SUM() function is as follows:
SELECT SUM(column_name)
FROM table_name
WHERE condition;
This query will return the sum of all Non-Null values in a particular column.
Example#
Let’s say we apply the SUM function to the salary column:
The following code shows the SQL query:
As we can see in the output above, the sum of all Non-Null values in the salary column is 290,000.
The AVG function#
The AVG() function returns the average value of a numeric column.
Syntax#
The syntax for the AVG() function is as follows:
SELECT AVG(column_name)
FROM table_name
WHERE condition;
This query will return the average of all Non-Null values in a particular column.
Example#
Let’s say we apply the AVG function to the salary column:
The following code shows the SQL query:
As we can see, it returned the average of Non-Null values of the column salary, i.e. 48333.33.
The MAX function#
The MAX() function returns the largest value of the selected column.
Syntax#
The syntax for the MAX() function is as follows:
SELECT MAX(column_name)
FROM table_name
WHERE condition;
This query will return the max of all Non-Null values in a particular column.
Example#
Let’s say we want to find the highest salary in the CUSTOMERS table:
The following code shows the SQL query:
The MIN function#
The MIN() function returns the smallest value in the selected column.
Syntax#
The syntax for the MIN() function is as follows:
SELECT MIN(column_name)
FROM table_name
WHERE condition;
This query will return the min of all Non-Null values in a particular column.
Example#
Let’s say we want to find the lowest salary in the CUSTOMERS table:
The following code shows the SQL query:
Quick quiz!#
Which of the following SQL queries will return the youngest person in the CUSTOMERS table?
A)
SELECT AVG(AGE)
FROM CUSTOMERS;
B)
SELECT MAX(AGE)
FROM CUSTOMERS;
C)
SELECT MIN(AGE)
FROM CUSTOMERS;
D)
SELECT COUNT(AGE)
FROM CUSTOMERS;
In the next lesson, we will discuss two important clauses: ORDER BY and GROUP BY.
